package com.skyworth.acreate_mybatis.util;

import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.skyworth.util.PUtils;

public class SqlToJavaBean {
	static String newLine = "\r\n";
	public String driverClass = "com.mysql.cj.jdbc.Driver";
	public String sqlURL = "jdbc:mysql://172.20.248.220:3306/mes?useUnicode=true&characterEncoding=gbk&zeroDateTimeBehavior=convertToNull";
	public String user = "root";
	public String password = "le98i3nf";
	public String path = "";
	public String tablename = "";
	public String[] colnames;
	public String[] colTypes;
	public int[] colSizes; // 列名大小
	public int[] colScale; // 列名小数精度
	public boolean importUtil = false;
	public boolean importSql = false;
	public boolean importMath = false;
	
	private String delete="deleteByPrimaryKey";
	private String update="updateByPrimaryKeySelective";

	public List<String> primaryKey = new ArrayList<String>();;
	
	public String flag="`";

	/**
	 * @param args
	 */
	public void doTableToEntity(String tName) {
		tablename = tName;
		// 数据连Connection获取,自己想办法就行.
		Connection conn = getConnection();
		String strsql = "SELECT * FROM " + tablename;// +" WHERE ROWNUM=1"
		// 读一行记录;
		try {
			// System.out.println(strsql);
			PreparedStatement pstmt = conn.prepareStatement(strsql);
			pstmt.executeQuery();
			ResultSetMetaData rsmd = pstmt.getMetaData();
			int size = rsmd.getColumnCount(); // 共有多少列
			colnames = new String[size];
			colTypes = new String[size];
			colSizes = new int[size];
			colScale = new int[size];
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				rsmd.getCatalogName(i + 1);
				colnames[i] = rsmd.getColumnName(i + 1).trim();
				colTypes[i] = rsmd.getColumnTypeName(i + 1).toLowerCase();
				colScale[i] = rsmd.getScale(i + 1);
				// System.out.println(rsmd.getCatalogName(i + 1));
				if ("datetime".equals(colTypes[i])) {
					importUtil = true;
				}
				if ("image".equals(colTypes[i]) || "text".equals(colTypes[i])) {
					importSql = true;
				}
				if (colScale[i] > 0) {
					importMath = true;
				}
				colSizes[i] = rsmd.getPrecision(i + 1);
			}
			String content = parse(colnames, colTypes, colSizes);
			try {
				FileWriter fw = new FileWriter(initcap(tablename) + ".java");
				PrintWriter pw = new PrintWriter(fw);
				pw.println(content);
				pw.flush();

				pw.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
	}

	/**
	 * 解析处理(生成实体类主体代码)
	 */
	private String parse(String[] colNames, String[] colTypes, int[] colSizes) {
		StringBuffer sb = new StringBuffer();
		sb.append("\r\nimport java.io.Serializable;" + newLine);
		sb.append("import javax.persistence.Entity;" + newLine);
		sb.append("import javax.persistence.Table;" + newLine);
		sb.append("import javax.persistence.Id;" + newLine);
		if (importUtil) {
			sb.append("import java.util.Date;\r\n");
		}
		if (importSql) {
			sb.append("import java.sql.*;\r\n\r\n");
		}
		if (importMath) {
			sb.append("import java.math.*;\r\n\r\n");
		}

		sb.append(newLine + newLine + newLine);
		// 表注释
		// processColnames(sb);
		sb.append("@Entity" + "\r\n");
		sb.append(String.format("@Table(name=\"%s\")", tablename) + "\r\n");
		sb.append("public class " + initcap(tablename) + " implements Serializable {\r\n");
		processAllAttrs(sb);
		processAllMethod(sb);
		sb.append("}\r\n");
		System.out.println(sb.toString());
		return sb.toString();

	}

	/**
	 * 处理列名,把空格下划线'_'去掉,同时把下划线后的首字母大写 要是整个列在3个字符及以内,则去掉'_'后,不把"_"后首字母大写.
	 * 同时把数据库列名,列类型写到注释中以便查看,
	 * 
	 * @param sb
	 */
	public void processColnames(StringBuffer sb) {
		sb.append("\r\n/** " + tablename + "\r\n");
		String colsiz = "";
		for (int i = 0; i < colnames.length; i++) {
			colsiz = colSizes[i] <= 0 ? ""
					: (colScale[i] <= 0 ? "(" + colSizes[i] + ")" : "(" + colSizes[i] + "," + colScale[i] + ")");
			sb.append("\t" + colnames[i].toUpperCase() + " " + colTypes[i].toUpperCase() + colsiz + "\r\n");
			char[] ch = colnames[i].toCharArray();
			char c = 'a';
			if (ch.length > 3) {
				for (int j = 0; j < ch.length; j++) {
					c = ch[j];
					if (c == '_') {
						if (ch[j + 1] >= 'a' && ch[j + 1] <= 'z') {
							ch[j + 1] = (char) (ch[j + 1] - 32);
						}
					}
				}
			}
			String str = new String(ch);
			colnames[i] = str.replaceAll("_", "");
		}
		sb.append("*/\r\n");
	}

	/**
	 * 生成所有的方法
	 * 
	 * @param sb
	 */
	private void processAllMethod(StringBuffer sb) {
		for (int i = 0; i < colnames.length; i++) {
			sb.append("\tpublic void set" + initcap(colnames[i]) + "("
					+ SqlTypeToJavaType(colTypes[i], colScale[i], colSizes[i]) + " " + colnames[i] + "){\r\n");
			sb.append("\t\tthis." + colnames[i] + "=" + colnames[i] + ";\r\n");
			sb.append("\t}\r\n");

			sb.append("\tpublic " + SqlTypeToJavaType(colTypes[i], colScale[i], colSizes[i]) + " get"
					+ initcap(colnames[i]) + "(){\r\n");
			sb.append("\t\treturn " + colnames[i] + ";\r\n");
			sb.append("\t}\r\n");
		}
	}

	/**
	 * 解析输出属性
	 * 
	 * @return
	 */
	private void processAllAttrs(StringBuffer sb) {
		sb.append("\tprivate static final long serialVersionUID = 1L;\r\n");
		for (int i = 0; i < colnames.length; i++) {
			sb.append("\tprivate " + SqlTypeToJavaType(colTypes[i], colScale[i], colSizes[i]) + " " + colnames[i]
					+ ";\r\n");
		}
		sb.append("\r\n");
	}

	/**
	 * 把输入字符串的首字母改成大写
	 * 
	 * @param str
	 * @return
	 */
	private String initcap(String str) {
		char[] ch = str.toCharArray();
		if (ch[0] >= 'a' && ch[0] <= 'z') {
			ch[0] = (char) (ch[0] - 32);
		}
		return new String(ch);
	}

	/**
	 * Oracle
	 * 
	 * @param sqlType
	 * @param scale
	 * @return
	 */
	private String SqlTypeToJavaType(String sqlType, int scale, int size) {
		if (driverClass.toLowerCase().contains("oracle"))
			return oracleToJava(sqlType, scale, size);

		if (driverClass.toLowerCase().contains("mysql"))
			return oracleToJava(sqlType, scale, size);

		return null;
	}

	private String oracleToJava(String sqlType, int scale, int size) {
		if (sqlType.equals("tinyint")) {
			if (size == 1)
				return "Boolean";
			else
				return "Byte";
		} else if (sqlType.equals("integer") || sqlType.equals("int")) {
			return "Integer";
		} else if (sqlType.equals("bit")) {
			return "Boolean";
		} else if (sqlType.equals("long") || sqlType.equals("bigint")) {
			return "Long";
		} else if (sqlType.equals("float") || sqlType.equals("float precision"))
			return "Float";
		else if (sqlType.equals("double") || sqlType.equals("double precision")) {
			return "Double";
		} else if (sqlType.equals("number") || sqlType.equals("decimal") || sqlType.equals("numeric")
				|| sqlType.equals("real")) {
			return scale == 0 ? (size < 10 ? "Integer" : "Long") : "BigDecimal";
		} else if (sqlType.equals("varchar") || sqlType.equals("varchar2") || sqlType.equals("char")
				|| sqlType.equals("nvarchar") || sqlType.equals("nchar")) {
			return "String";
		} else if (sqlType.equals("datetime") || sqlType.equals("date") || sqlType.equals("timestamp")) {
			return "Date";
		}
		System.out.println("***************************************** " + sqlType);
		return null;
	}

	/**
	 * 自动获取jcbc链接
	 */
	protected Connection getConnection() {
		Connection localConnection = null;
		try {
			Class.forName(driverClass);
			localConnection = DriverManager.getConnection(sqlURL, user, password);
			localConnection.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return localConnection;
	}

	/**
	 * 释放jdbc链接
	 * 
	 * @param conn
	 */
	protected static void releaseConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}

	public void getTables(String database) {
		String strsql = "select table_name from information_schema.tables where table_schema='" + database
				+ "' and table_type='base table'";

		// 数据连Connection获取,自己想办法就行.
		Connection conn = getConnection();

		try {
			System.out.println(strsql);
			PreparedStatement pstmt = conn.prepareStatement(strsql);
			ResultSet resultSet = pstmt.executeQuery();
			while (resultSet.next()) {
				System.out.println(resultSet.getString("table_name"));
			}
			// String content = parse(colnames, colTypes, colSizes);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
	}

	private List<String> getIdName(Connection conn, String tableName) {

//		while(primaryKeyResultSet.next()){  
//		    String primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");  
//		} 

		primaryKey.clear();
		DatabaseMetaData metaData = null;
		try {
			metaData = conn.getMetaData();
			// ResultSet rs = metaData.getColumns(conn.getCatalog(), "%", tableName, "%ID");
			ResultSet rs = metaData.getPrimaryKeys(conn.getCatalog(), null, tableName);

			while (rs.next()) {
				primaryKey.add(rs.getString("COLUMN_NAME"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return primaryKey;
	}

	//其他数据库不需要这个方法 oracle和db2需要
		private static String getSchema(Connection conn) throws Exception {
			String schema;
			schema = conn.getMetaData().getUserName();
			if ((schema == null) || (schema.length() == 0)) {
				throw new Exception("ORACLE数据库模式不允许为空");
			}
			return schema.toUpperCase().toString();
	 
		}
 
		
	public void getTableInfo(String tName) throws Exception {
		tablename = tName;
		// 数据连Connection获取,自己想办法就行.
		Connection conn = getConnection();
		String strsql = "SELECT * FROM " + tablename;// +" WHERE ROWNUM=1"
		Map<String,String> map = new HashMap();
		// 读一行记录;
		try {
			// System.out.println(strsql);
			PreparedStatement pstmt = conn.prepareStatement(strsql);
			getIdName(conn, tName);
			pstmt.executeQuery();
			ResultSetMetaData rsmd = pstmt.getMetaData();
		
			ResultSet resultSet = conn.getMetaData().getTables(null, "%", tName, new String[] { "TABLE" });	
			while (resultSet.next()) {
		    	String tableName=resultSet.getString("TABLE_NAME");
		    	System.out.println(tableName);
		    	
		    	if(tableName.equals(tName)){
		    		ResultSet rs = conn.getMetaData().getColumns(null, getSchema(conn),tableName, "%");
		    		while(rs.next()){
		    			//System.out.println("字段名："+rs.getString("COLUMN_NAME")+"--字段注释："+rs.getString("REMARKS")+"--字段数据类型："+rs.getString("TYPE_NAME"));		    			
		    			String colName = rs.getString("COLUMN_NAME");
		    			String remarks = rs.getString("REMARKS");	    		 
		    			map.put(colName,remarks);

		    		}
		    	}
			}

 
			
			
//			while (resultSet.next()) {
//		    	String tableName=resultSet.getString("TABLE_NAME");
//		    	System.out.println(tName);
//		    	
//		    	if(tableName.equals(table)){
//		    		ResultSet rs = conn.getMetaData().getColumns(null, getSchema(conn),tableName.toUpperCase(), "%");
// 
//		    		
//		    	}

	
			// pstmt.get
			int size = rsmd.getColumnCount(); // 共有多少列
			TableInfo[] ts = new TableInfo[size];
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				
				String cnames = rsmd.getColumnName(i).trim();
				String cTypes = rsmd.getColumnTypeName(i).toLowerCase();
				String cClassType = rsmd.getColumnClassName(i);
				 
				
				int cScale = rsmd.getScale(i);
				int cSizes = rsmd.getPrecision(i);
//				System.out.println(rsmd.getColumnLabel(i));
//				System.out.println(rsmd.getColumnTypeName(i));
//				System.out.println(rsmd.getColumnClassName(i));
//				System.out.println(rsmd.getSchemaName(i));	
				if(cTypes.toUpperCase().equals("TEXT")||cTypes.toUpperCase().equals("LONGTEXT"))
					cTypes="LONGVARCHAR";
				TableInfo t = new TableInfo(cnames, cTypes, cClassType, cScale, cSizes,map.get(cnames));
				ts[i - 1] = t;
			}

			for (TableInfo tableInfo : ts) {
				System.out.println(tableInfo.toJavaProperties());
			}
			System.out.println(newLine);
				
			System.out.println(bulidResultMap(ts));
			//System.out.println(bulidBase_Column_List(tName,ts));	
			System.out.println(bulidBase_selectByPrimaryKey(tName,ts));	
			System.out.println(bulidLimit());
			System.out.println(bulidFilter(ts));
			//System.out.println(bulidResultMap(ts));
			System.out.println(bulidInsert(tName, ts));
			System.out.println(bulidDelete(tName));
			System.out.println(bulidUpdate(tName, ts));
			
			
			System.out.println(bulidCount(tName));
			System.out.println(bulidGetList(tName));
//			System.out.println(bulidGetById(tName));
//			System.out.println(bulidGetByName(tName));
//			System.out.println(bulidGetByCode(tName));
			

			// pro

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
	}

	private String bulidResultMap(TableInfo... ts) {
		String line1 = "<resultMap id=\"BaseResultMap\" type=\"\">" + newLine;
		String line = "";
		for (TableInfo t : ts) {
			String name = t.colname;
			String type = t.colType.toUpperCase();
			line = line + "  <result column=\"" + name + "\" 		property=\"" + name + "\" 		jdbcType=\"" + type
					+ "\"/>" + newLine;
		}

		String line2 = "</resultMap>" + newLine;

		return (line1 + line + line2).replace("jdbcType=\"INT\"", "jdbcType=\"INTEGER\"");
	}

	private String bulidLimit() {
		String line = "<sql id=\"addLimit\">\r\n" + "  <if test=\"start!=null\"> limit #{start},#{limit} </if>\r\n"
				+ "</sql>";

		return line + newLine;
	}


	    
	    
     private String bulidBase_Column_List(String tableName, TableInfo... ts) {

			String line1 = "<sql id=\"Base_Column_List\">" + newLine;
			String lineK = "  ";
		 
			for (TableInfo t : ts) {
				String name = t.colname;
			 
				lineK = lineK + name + ",";
				 
			}
			lineK = PUtils.removeEndChar(lineK);
		 
			String line = lineK+ newLine;
			String line2 = "</sql>" + newLine;

			return line1 + line + line2;

		}
     
     private String bulidBase_selectByPrimaryKey(String tableName, TableInfo... ts) {

   	  
	    
 
	  
          String line1="<select id=\"selectByPrimaryKey\"  resultMap=\"BaseResultMap\">"+newLine;
          String line2="  select * from "+tableName+" ";
          String line=bulidWhereId();
          String line3="</select>"+newLine;

			return line1 +line2+ line + line3;

		}
		
	private String bulidFilter(TableInfo... ts) {
		String line1 = "<sql id=\"filters\">" + newLine;
		String line2 = "  <where>" + newLine;
		String line = "";
		for (TableInfo t : ts) {
			String name = t.colname;
			// String type = t.classType.toUpperCase();
			line = line + "    <if test=\"" + name + "!=null\"> and " +flag +name +flag+ "=#{" + name + "} </if>" + newLine;
		}
		String line3 = "</where>" + newLine;
		String line4 = "</sql>" + newLine;
		return line1 + line2 + line + line3 + line4;
	}

	private String bulidInsert(String tableName, TableInfo... ts) {

		String line1 = "<insert id=\"insert\" >" + newLine;
		String lineK = "";
		String lineV = "";
		for (TableInfo t : ts) {
			String name = t.colname;
			String nmaev = "#{" + name + "}";
			lineK = lineK +flag+name +flag+ ",";
			lineV = lineV + nmaev + ",";
		}
		lineK = PUtils.removeEndChar(lineK);
		lineV = PUtils.removeEndChar(lineV);
		String line = " insert into " + tableName + " ( " + lineK + ") values (" + lineV + ")" + newLine;
		String line2 = "</insert>" + newLine;

		return line1 + line + line2;

	}
	

 
    private String bulidWhereId()
    {
		String line5="where ";
		int i = 1;
		for (String k : primaryKey) {
			if (i == 1)
				line5 = line5 + k + "=" + "#{" + k + "}";
			else
				line5 = line5 + " and " + k + "=" + "#{" + k + "}";

			i++;
		}
		line5=line5+";"+newLine;
		
		if(line5.trim().length()==0)
			line5 = "  where id=#{id};" + newLine;
		return line5;
    }
	private String bulidUpdate(String tableName, TableInfo... ts) {

		String line1 = "<update id=\""+update+"\" >" + newLine;
		String line2 = "  update "+tableName + newLine;
		String line3 = "  <trim prefix=\"set\" suffixOverrides=\",\">" + newLine;
		String line4 = "  </trim>" + newLine;
		//String line5 = "  where id=#{id};" + newLine;
		String line5=bulidWhereId();
 
		String line6 = "</update>" + newLine;
		
		String line = "";
		for (TableInfo t : ts) {
			String name = t.colname;
			// String type = t.classType.toUpperCase();
			line = line + "    <if test=\""+name+"!=null\">"+flag+name+flag+"=#{"+name+"},</if>" + newLine;
		}
		 

		return line1  + line2 + line3 +line+ line4 + line5 + line6 ;

	}

	private String bulidDelete(String tableName) {

		String line1 = "<delete id=\""+delete+"\">";
		String line = "  delete from " + tableName + "  ";
		line =line+ bulidWhereId();
		String line2 = "</delete>";

		return line1+newLine + line + line2 + newLine;
	}

	private String bulidGetById(String tableName) {
		String line = "<select id=\"getById\" resultMap=\"BaseResultMap\">\r\n" + "         select * from " + tableName
				+ " where id=#{id}\r\n" + "</select>";
		return line+newLine;
	}

	private String bulidGetByName(String tableName) {
		String line = "<select id=\"getByName\" resultMap=\"BaseResultMap\">\r\n" + "         select * from "
				+ tableName + " where name=#{name}\r\n" + "</select>";
		return line+newLine;
	}

	private String bulidGetByCode(String tableName) {
		String line = "<select id=\"getByCode\" resultMap=\"BaseResultMap\">\r\n" + "         select * from "
				+ tableName + " where code=#{code}\r\n" + "</select>";
		return line+newLine;
	}
	
	private String bulidGetList(String tableName)
	{
		String line="";
		if(primaryKey.isEmpty())
		  line="<select id=\"getList\" resultType=\"java.util.List\" resultMap=\"BaseResultMap\">\r\n" + 
				"    select * from "+tableName+" <include refid=\"filters\"></include>    <include refid=\"addLimit\"></include>;\r\n" + 
				"</select>";
		else
		 line="<select id=\"getList\" resultType=\"java.util.List\" resultMap=\"BaseResultMap\">\r\n" + 
						"     select * from "+tableName+" <include refid=\"filters\"></include>  order by "+primaryKey.get(0)+" <include refid=\"addLimit\"></include>;\r\n" + 
						"</select>";
				return line+newLine;
	}

	private String bulidCount(String tableName) {
		String line = "<select id=\"count\" resultType=\"java.lang.Integer\">\r\n" + "    select count(*) from "
				+ tableName + " <include refid=\"filters\"></include>;\r\n" + "</select>";
		return line+newLine;
	}

	static class TableInfo {
		private String colname;
		private String colType;
		private String classType;
		private int colSize;
		private int colScale;
		private String reMark;

		public TableInfo() {
			super();
			// TODO Auto-generated constructor stub
		}

		public TableInfo(String colname, String colType, String classType, int colSize, int colScale,String reMark) {

			super();
			this.colname = colname;
			this.colType = colType;
			this.classType = classType;
			this.colSize = colSize;
			this.colScale = colScale;
			this.reMark=reMark;
		}

		public String getColname() {
			return colname;
		}

		public void setColname(String colname) {
			this.colname = colname;
		}

		public String getColType() {
			return colType;
		}

		public void setColType(String colType) {
			this.colType = colType;
		}

		public int getColSize() {
			return colSize;
		}

		public void setColSize(int colSize) {
			this.colSize = colSize;
		}

		public int getColScale() {
			return colScale;
		}

		public void setColScale(int colScale) {
			this.colScale = colScale;
		}

		public String getClassType() {
			return classType;
		}

		public void setClassType(String classType) {
			this.classType = classType;
		}
		
		

		public String getReMark() {
			return reMark;
		}

		public void setReMark(String reMark) {
			this.reMark = reMark;
		}

		public String toString() {
			return colname + "|" + colType + "|" + classType;
		}

		public String findClaasNameType() {
			return classType.replace("java.lang.", "").replace("java.math.", "");
		}

		public String toJavaProperties() {
			return "private		" + findClaasNameType() + "		" + colname + ";"+"//"+reMark;
		}

	}

	public static void main(String[] args) {
		SqlToJavaBean t = new SqlToJavaBean();
		t.driverClass = "com.mysql.cj.jdbc.Driver";
		t.sqlURL = "jdbc:mysql://172.20.248.220:3306/mes?useUnicode=true&characterEncoding=gbk&zeroDateTimeBehavior=convertToNull";
		t.sqlURL = "jdbc:mysql://192.168.13.85:3306/qxftest?useUnicode=true&characterEncoding=utf-8";

		t.user = "root";
		t.password = "le98i3nf";
		t.password = "root";
		// t.getTables("qxftest");
		t.doTableToEntity("NODE_SITES");
		// t.doTableToEntity("base_meterial_group");
	}

}
